package com.astrocsr.dao;

import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.astrocsr.common.db.SearchQueryTemplate;
import com.astrocsr.domain.bean.TblGroup;
import com.astrocsr.domain.bean.TblUser;
import com.astrocsr.domain.condition.SecCondition;
import com.astrocsr.domain.db.PagingInfo;
import com.astrocsr.domain.db.SearchResult;

@Repository("groupDAO")
public class GroupDAO extends BaseHelperDAO {

	public static final String NAME 	 	= "name";
	public static final String GROUP_CODE 	= "groupCode";
	public static final String PARENT_ID 	= "parentId";

	public TblGroup findByGroupCode(String groupCode)
			throws Exception {
		try {

			String queryString = "from TblGroup as model where model.groupCode = :groupCode";
			Query query = getCurrentSession().createQuery(queryString);
			query.setParameter("groupCode", groupCode);
			query.setMaxResults(1);
			return (TblGroup)query.uniqueResult();
		} catch (RuntimeException re) {
			throw re;
		}
	}

	@SuppressWarnings("unchecked")
	public List<TblGroup> listTblGroup() throws Exception {
		List<TblGroup> result = null;

		String queryString = "from TblGroup"; // Hibernate Query Language
		result = getCurrentSession().createQuery(queryString).list();

		return result;
	}

	/**
	 * Insert customer into database
	 */
	public void addTblGroup(TblGroup entity) throws Exception {
		// Insert into database
		getCurrentSession().save(entity);
	}
	
	@SuppressWarnings("unchecked")
	public List<TblGroup> listGroupHierarchy(String startGroupCode) throws Exception {
		List<TblGroup> result = null;
		try {
			String queryString = "select tg.* from Tbl_Group tg start with tg.group_code = :startGroupCode" +
									" connect by prior tg.group_code = tg.parent_code";
			
			Query query = getCurrentSession().createSQLQuery(queryString).addEntity(TblGroup.class);
			query.setParameter("startGroupCode", startGroupCode);
			
			result = query.list();
		} catch (RuntimeException re) {
			throw re;
		}
		return result;
	}

	public SearchResult<TblGroup> listGroupByParentCode(SecCondition condition, PagingInfo pagingInfo, boolean withParent) throws Exception {
		SearchResult<TblGroup> result = null;
		try {

			String orderColumn = "";
			SearchQueryTemplate searchQueryTemplate = new SearchQueryTemplate(orderColumn, pagingInfo);
			searchQueryTemplate.setQueryString("from TblGroup model ");
			
			if (condition != null) {
				if (StringUtils.isNotBlank(condition.getGroupParent())) {
					String groupCodeExp = "(model.parentCode = :parentCode)";
					if (withParent) {
						groupCodeExp = "(" + groupCodeExp + " or (model.groupCode = :parentCode) " + ")";
					}
					searchQueryTemplate.setExpression(groupCodeExp, "parentCode", condition.getGroupParent());
				}
				
				if (StringUtils.isNotBlank(condition.getGroupName())) {
					String groupName = "%" + condition.getGroupName() + "%";
					searchQueryTemplate.setExpression("model.groupName like :groupName", "groupName", groupName);
				}
				
				if (StringUtils.isNotBlank(condition.getGroupCode())) {
					String groupCode = "%" + condition.getGroupCode() + "%";
					searchQueryTemplate.setExpression("model.groupCode like :groupCode", "groupCode", groupCode);
				}
			}
			
			// execute search with paging
			result = this.find(searchQueryTemplate);

		} catch (RuntimeException re) {
			throw re;
		}
		return result;
	}
	
	public void deleteGroup(String groupCode) {
		Query query = getCurrentSession().createQuery("DELETE from TblGroup WHERE groupCode =:groupCode ");
		query.setParameter("groupCode", groupCode);
		query.executeUpdate();
	}

	public TblGroup getGroupByCode(String groupCode) throws Exception {
		TblGroup result = null;
		try {
			String queryString = "from TblGroup tg where " +
									" tg.groupCode = :groupCode";
			
			Query query = getCurrentSession().createQuery(queryString);
			query.setParameter("groupCode", groupCode);
			query.setMaxResults(1);
			result = (TblGroup)query.uniqueResult();
		} catch (RuntimeException re) {
			throw re;
		}
		return result;
	}

	/**
	 * Update Group
	 */
	public void updateGroup(TblGroup bean) throws Exception {

		Query query = getCurrentSession().createQuery("UPDATE TblGroup set groupName=:groupName, updatedBy=:updatedBy,updatedDate=:updatedDate WHERE groupCode=:groupCode ");
		query.setParameter("groupName", 	bean.getGroupName());
		query.setParameter("groupCode", 	bean.getGroupCode());
		query.setParameter("updatedBy", 	bean.getUpdatedBy());
		query.setParameter("updatedDate", 	bean.getUpdatedDate());
		query.executeUpdate();
	}
}